﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BEELab.Data;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BEELab.Logic.ProgramManagement.StageManagement
{
    public class BlockElements
    {
        public class BlockElement
        {
            public SortedDictionary<int, int> Children;

            /// <summary>
            /// Constructor for block element - get BlockID and DataTable containing all blocks,
            /// select from Data Table the row of given block ID and creates new Block as a duplication of given block
            /// </summary>
            /// <param name="ID">Block ID to duplicate</param>
            /// <param name="data">Data Table </param>
            public BlockElement(int ID, DataTable data)
            {
                DataRow[] rows = data.Select("ID=" + ID);
                if (rows.Length == 1)
                {
                    this.id = int.Parse(rows[0]["ID"].ToString());
                    this.name = rows[0]["Name"].ToString();
                    this.description = rows[0]["Description"].ToString();
                    this.content = rows[0]["Content"].ToString();
                    this.type = rows[0]["Type"].ToString();

                    this.stageID = int.Parse(rows[0]["StageID"].ToString());
                    this.parentID = int.Parse(rows[0]["ParentID"].ToString());
                    this.place = int.Parse(rows[0]["Place"].ToString());

                    if (this.Type == "Block")
                    {
                        this.Children = new SortedDictionary<int, int>();

                        rows = data.Select("ParentID=" + this.id);

                        foreach (DataRow row in rows)
                        {
                            this.Children[int.Parse(row["Place"].ToString())] = int.Parse(row["ID"].ToString());
                        }
                    }
                }
            }

            /// <summary>
            /// Constructor - gets ElementID, retreives its data from DB and creates dupicatation of this element
            /// </summary>
            /// <param name="ID">Element ID</param>
            public BlockElement(int ID)
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "SELECT * FROM BlockElements WHERE ID=@ID";
                command.Parameters.Add(new SqlParameter("@ID", ID));
                DataTable dt = DBHelper.ExecuteQuery(command);

                if (dt.Rows.Count == 1)
                {
                    DataRow dr = dt.Rows[0];
                    this.id = ID;
                    this.type = dr["Type"].ToString();
                    this.stageID = int.Parse(dr["StageID"].ToString());
                    this.parentID = int.Parse(dr["ParentID"].ToString());
                    this.place = int.Parse(dr["Place"].ToString());
                    this.name = dr["Name"].ToString();
                    this.description = dr["Description"].ToString();
                    this.content = dr["Content"].ToString();
                    this.isvisible = bool.Parse(dr["isVisible"].ToString());

                    if (this.Type == "Block")
                    {
                        this.Children = new SortedDictionary<int, int>();
                        List<int> list = ListByParentID(this.id);
                        for (int i = 0; i < list.Count; i++)
                        {
                            this.Children[i] = list[i];
                        }
                    }
                }
            }

            private int id;
            public int ID
            {
                get { return id; }
            }

            private string type;
            public string Type
            {
                get { return type; }
            }

            private int stageID;
            public int StageID
            {
                get { return stageID; }
            }

            private int parentID;
            public int ParentID
            {
                get { return parentID; }
                set {
                    parentID = value;
                    UpdateElement(this.ID, this.ParentID, this.Place, this.Name, this.Description, this.Content); 
                }
            }

            private bool isvisible;
            public Boolean isVisible
            {
                get { return isvisible; }
                set
                {
                    isvisible = value;
                }
            }

            private int place;
            public int Place
            {
                get { return place; }
                set {
                    this.place = value;
                    UpdateElement(this.ID, this.ParentID, this.Place, this.Name, this.Description, this.Content); 
                }
            }

            private string name;
            public string Name
            {
                get { return name; }
            }

            private string description;
            public string Description
            {
                get { return description; }
            }

            private string content;
            public string Content
            {
                get { return content; }
            }
        }

        /// <summary>
        /// Gets element ID and creates a duplicatation of this element, and inserts it into DB with new ID.
        /// </summary>
        /// <param name="ElementID">Element ID to duplicate</param>
        /// <param name="StageID">StageID of this element</param>
        /// <param name="NamePrefix">Name Prefix</param>
        /// <returns>New element ID number</returns>
        public static int ImportElement(int ElementID, int StageID, string NamePrefix)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT * FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ElementID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            if (dt.Rows.Count != 1)
                return -1;

            DataRow dr = dt.Rows[0];

            if (dr["Type"].ToString().Equals("Call"))
            {
                dr["Content"] = NamePrefix + dr["Content"].ToString();
            }

            int ID = CreateElement(dr["Type"].ToString(), StageID, int.Parse(dr["ParentID"].ToString()), int.Parse(dr["Place"].ToString()), NamePrefix + dr["Name"].ToString(), dr["Description"].ToString(), dr["Content"].ToString(), bool.Parse(dr["isVisible"].ToString()));

            return ID;
        }

        /// <summary>
        /// Creates new element with all given values, and insert it to DB
        /// </summary>
        /// <param name="Type">Element Type</param>
        /// <param name="StageID">Element StageID</param>
        /// <param name="ParentID">Element ParentID</param>
        /// <param name="Place">Element Place</param>
        /// <param name="Name">Element Name</param>
        /// <param name="Description">Element Description</param>
        /// <param name="Content">Element Content</param>
        /// <param name="isVisible">Element isVisible property</param>
        /// <returns>Number of rows effected on DB</returns>
        public static int CreateElement(string Type, int StageID, int ParentID, int Place, string Name, string Description, string Content, Boolean isVisible)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO BlockElements (Type, StageID, ParentID, Place, Name, Description, Content, isVisible) VALUES (@Type, @StageID, @ParentID, @Place, @Name, @Description, @Content, @isVisible); SELECT SCOPE_IDENTITY();";
            command.Parameters.Add(new SqlParameter("@Type", Type));
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            command.Parameters.Add(new SqlParameter("@ParentID", ParentID));
            command.Parameters.Add(new SqlParameter("@Place", Place));
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@Content", Content));
            command.Parameters.Add(new SqlParameter("@isVisible", isVisible));

            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Gets stage ID and Block Name and returns Block ID from DB
        /// </summary>
        /// <param name="StageID">Stage ID</param>
        /// <param name="Name">Block Name</param>
        /// <returns>Block ID from DB, or 0 if block didn't found</returns>
        public static int GetBlockIDByName(int StageID, string Name)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM BlockElements WHERE StageID=@StageID AND Name=@Name";
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@StageID", StageID));

            string res = DBHelper.ExecuteScalar(command);
            if (res == null)
                return 0;

            return int.Parse(res);
        }

        /// <summary>
        /// Gets Block ID and returns its parent block ID from DB
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <returns>Parent block ID from DB</returns>
        public static int GetParentID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ParentID FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Gets block ID and return block place from DB
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <returns>Block place number, or -1 if not found</returns>
        public static int GetPlace(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Place FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            string res = DBHelper.ExecuteScalar(command);

            if (res == null)
                return -1;

            return int.Parse(res);
        }

        /// <summary>
        /// Gets Element ID and delete it from DB, in addition delee all of this element related information
        /// </summary>
        /// <param name="ID">Element ID</param>
        /// <returns>True if deleted successfuly, false otherwise</returns>
        public static Boolean DeleteElement(int ID)
        {
            BlockElement element = new BlockElement(ID);

            //Delete all related blocks of this element
            foreach (int id in ListByParentID(ID))
            {
                DeleteElement(id);
            }

            //Delete the element itself
            ProgramManagement.StageManagement.ContainerManagement.RemoveElement("BlockElement", ID);

            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);
            if (res == 0)
                return false;

            if (element.ParentID == 0)
                return true;

            //Decrement place for all those bigger than this
            command = new SqlCommand();
            command.CommandText = "Update BlockElements SET BlockElements.Place=BlockElements.Place-1 WHERE ParentID=@ParentID AND Place > @Place";
            command.Parameters.Add(new SqlParameter("@ParentID", element.ParentID));
            command.Parameters.Add(new SqlParameter("@Place", element.Place));
            res = DBHelper.ExecuteNonQuery(command);

            return true;
        }

        /// <summary>
        /// Gets a blockID needs to be updated, and the new values ​​to update on this block
        /// and updates the block details on DB
        /// </summary>
        /// <param name="ID">Block ID that needed to be updated</param>
        /// <param name="ParentID">new Parent ID</param>
        /// <param name="Place">new Place</param>
        /// <param name="Name">new Name</param>
        /// <param name="Description">new Description</param>
        /// <param name="Content">new Content</param>
        /// <returns>True if update succeeded, False otherwise</returns>
        public static Boolean UpdateElement(int ID, int ParentID, int Place, string Name, string Description, string Content)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE BlockElements SET ParentID=@ParentID, Place=@Place, Name=@Name, Description=@Description, Content=@Content WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ParentID", ParentID));
            command.Parameters.Add(new SqlParameter("@Place", Place));
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@Content", Content));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Gets stage ID and returns a list of all block ID's that related to this stage
        /// </summary>
        /// <param name="ID">Stage ID</param>
        /// <returns>List of all block ID's that related to this stage</returns>
        public static List<int> ListByStageID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM BlockElements WHERE StageID=@StageID";
            command.Parameters.Add(new SqlParameter("@StageID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets ParentID and returns a list of all block element IDs that related to this parent block
        /// </summary>
        /// <param name="ID">Parent ID</param>
        /// <returns>list of all block element IDs that related to parentID</returns>
        public static List<int> ListByParentID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM BlockElements WHERE ParentID=@ParentID ORDER BY Place ASC";
            command.Parameters.Add(new SqlParameter("@ParentID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets BlockID and return Block Name from DB
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <returns>Block Name</returns>
        public static string GetNameByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Name FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets BlockID and returns the block type from DB  (Experiment/Session/Stage etc..)
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <returns>Block type</returns>
        public static string GetType(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Type FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets BlockID and returns block content from DB
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <returns>Block content</returns>
        public static string GetContent(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Content FROM BlockElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets BlockID and content as a string, and updates block's content on DB
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <param name="Content">Block's new content</param>
        public static void SetContent(int ID, string Content)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE BlockElements SET Content=@Content WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Content", Content));
            command.Parameters.Add(new SqlParameter("@ID", ID));
            DBHelper.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Gets Block ID and boolean value, and sets isVisible property of the block on DB according to givn value.
        /// </summary>
        /// <param name="ID">Block ID</param>
        /// <param name="isVisible">True if visible or False if not visible</param>
        public static void SetVisibility(int ID, Boolean isVisible)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE BlockElements SET isVisible=@isVisible WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@isVisible", isVisible));
            command.Parameters.Add(new SqlParameter("@ID", ID));
            DBHelper.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Gets Stage ID and returns string that contains all of this stage blocks names,
        /// seperate by seperator.
        /// </summary>
        /// <param name="StageID">Stage ID</param>
        /// <param name="separator">separator</param>
        /// <returns>String that contains all of this stage blocks names</returns>
        public static string EnumerateBlocksForStage(int StageID, string separator)
        {
            StringBuilder res = new StringBuilder();

            //Gets all block names of this stage
            SqlCommand command = new SqlCommand();
            command.CommandText = "Select Name FROM BlockElements WHERE StageID=@StageID";
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            bool isFirst = true;

            //Builds up a string of all block names, seperate by seperator
            foreach (DataRow row in dt.Rows)
            {
                if (!isFirst)
                {
                    res.Append(separator);
                }
                else
                {
                    isFirst = false;
                }
                res.Append(row[0].ToString());
                res.Append("$");
            }

            return res.ToString();
        }
    }
}